Group By Expression
Introduction
The previous examples illustrated how to group employees by department, where the database field dept_id was used as the field to group by. It is also possible to group where an expression defines the grouping criteria. This will be demonstrated in this example where the employee list will be grouped by the first letter of the employee last name. An expression will be developed to be used as the criteria for grouping.
The desired output is shown in the figure below:
The steps to follow are:
- Sort by Employee Last Name.
- Create a Group Band and set its background color to light gray.
- Create an expression to obtain the first letter of the last name and place it within the “Group-by-field or expression” box.
- Place the expression into the Group Band Header to highlight the groupings (see the bold letters in the above figure).
Note: When grouping by a particular field (Employee last name in this example), the data must be sorted by the same field.
Create the Basic Report
Begin by creating a report and within the “Edit Report” dialog box click the Sort Tab and sort by Employee Last Name.
Within the Edit Report dialog box, click the Design button to bring up the Band Report Editor and create a simple report as shown in the figure below:
Create a Band Group
Create a band group by clicking the “Create a Band Group” icon on the Band Report Editor toolbar. The Header dialog box will appear as shown below. Since grouping by the first letter of the employee last name is to be done, you need to enter an appropriate expression into the “Group-by-field or expression” box.
Add a color to the band by clicking the “Color” button, then select light gray.
Create the Expression
To create the expression, click the ellipsis button to the right of the “Group-by-field or expression” box to launch the Expression Wizard. Click Function, then select the COPY function.
The COPY function returns a substring, and for this example you need to choose the Employee Last Name field from the database (using the same method as in previous examples) and return the first character from the string by entering the following:
The expression to return the first letter of the last name has now been inserted into the Group-by-field or expression box. The grouping is now handled by an expression, as opposed to an earlier example where grouping was done by a database field name.
Place Expression into Group Header Band
The final step is to place the same expression into the Group Header such that the first letter of the last name is displayed within the Group Header. To accomplish this, click E=mc2 on the toolbar, then click within the Group Header Band. Then enter the same expression as before.
Drag the field containing the expression to the left edge, then set the font to 14 point bold.
The desired report will print as follows: